use chrono::{DateTime, Local};
use sqlite_wasm_rs::sqlite3_stmt;
use crate::db::dao::base_dao::{Adder};
use crate::db::db::{Db, DbParamType};
use crate::db::pojo::pojo::{ImSession};
use crate::db::util::{db_get_bool, db_get_int, db_get_int64, db_get_string};
use crate::util::time::parse_to_time;

const SELECT_COLUMNS : &str = "id, name, serverId, currentUserId, fromUserId, toUserId, groupCode, sessionType,
        hotMessageTotal, latelyMessage, status, isWaking, isTop, isDisturb, isWaking2, isTop2,
        isDisturb2, ext1, ext2, createTime, updateTime";

pub fn session_inserts(db:Db, sessions: &mut Vec<&mut ImSession>) {
    if sessions.is_empty() {return ();}
    let sql = "insert into im_session(currentUserId, name, serverId, fromUserId, toUserId, sessionType, groupCode, hotMessageTotal, latelyMessage,
        isWaking, isTop, isDisturb, isWaking2, isTop2, isDisturb2, status, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut params_list = Vec::new();
    for session in sessions.iter() {
        let mut param = Vec::new();
        param.push(DbParamType::Str(&*session.current_user_id));
        param.push(DbParamType::Str(&*session.name));
        param.push(DbParamType::OptionStr(session.server_id.as_ref()));
        param.push(DbParamType::Str(&*session.from_user_id));
        param.push(DbParamType::OptionStr(session.to_user_id.as_ref()));
        param.push(DbParamType::Byte(session.session_type.clone() as i8));
        param.push(DbParamType::OptionStr(session.group_code.as_ref()));
        param.push(DbParamType::Uint(session.hot_message_total as u64));
        param.push(DbParamType::Str(&*session.lately_message));
        param.push(DbParamType::Bool(session.is_waking.clone()));
        param.push(DbParamType::Bool(session.is_top.clone()));
        param.push(DbParamType::Bool(session.is_disturb.clone()));
        param.push(DbParamType::Bool(session.is_waking2.clone()));
        param.push(DbParamType::Bool(session.is_top2.clone()));
        param.push(DbParamType::Bool(session.is_disturb2.clone()));
        param.push(DbParamType::Int(session.status.clone() as i64));
        param.push(DbParamType::DateTime2(&session.create_time));
        param.push(DbParamType::DateTime2(&session.update_time));

        params_list.push(param);
    }

    log::info!("db inserts session params: {:?}", params_list);
    let ids = db.inserts2("session inserts", sql, params_list);
    assert_eq!(ids.len(), sessions.len());
    for (idx, id) in ids.into_iter().enumerate() {
        let session = sessions.get_mut(idx).unwrap();
        session.id = Some(id);
    }
}

pub fn session_insert(db:Db, session: &mut ImSession) {
    let sql = "insert into im_session(currentUserId, name, serverId, fromUserId, toUserId, sessionType, groupCode, hotMessageTotal, latelyMessage,
        isWaking, isTop, isDisturb, isWaking2, isTop2, isDisturb2, status, createTime, updateTime) values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    let mut param = Vec::new();
    param.push(DbParamType::Str(&*session.current_user_id));
    param.push(DbParamType::Str(&*session.name));
    param.push(DbParamType::OptionStr(session.server_id.as_ref()));
    param.push(DbParamType::Str(&*session.from_user_id));
    param.push(DbParamType::OptionStr(session.to_user_id.as_ref()));
    param.push(DbParamType::OptionStr(session.group_code.as_ref()));
    param.push(DbParamType::Uint(u64::from(session.hot_message_total)));
    param.push(DbParamType::Str(&*session.lately_message));
    param.push(DbParamType::Bool(session.is_waking.clone()));
    param.push(DbParamType::Bool(session.is_top.clone()));
    param.push(DbParamType::Bool(session.is_disturb.clone()));
    param.push(DbParamType::Bool(session.is_waking2.clone()));
    param.push(DbParamType::Bool(session.is_top2.clone()));
    param.push(DbParamType::Bool(session.is_disturb2.clone()));
    param.push(DbParamType::Int(session.status.clone() as i64));
    param.push(DbParamType::DateTime2(&session.create_time));
    param.push(DbParamType::DateTime2(&session.update_time));
    log::info!("db inserts session params: {:?}", param);
    let id = db.insert("session insert2", sql, param);
    session.id = Some(id);
}

pub fn session_update(db: Db, session: &mut ImSession) {
    let sql = "update im_session set name=?, serverId = ?, latelyMessage = ?, hotMessageTotal = ?, isWaking = ?, isTop = ?, isDisturb = ?,
        isWaking2 = ?, isTop2 = ?, isDisturb2 = ?, updateTime = ?, status=? where id = ?";
    let mut param = vec![];
    param.push(DbParamType::Str(&*session.name));
    param.push(DbParamType::OptionStr(session.server_id.as_ref()));
    param.push(DbParamType::Str(&*session.lately_message));
    param.push(DbParamType::Uint(session.hot_message_total as u64));
    param.push(DbParamType::Bool(session.is_waking.clone()));
    param.push(DbParamType::Bool(session.is_top.clone()));
    param.push(DbParamType::Bool(session.is_disturb.clone()));
    param.push(DbParamType::Bool(session.is_waking2.clone()));
    param.push(DbParamType::Bool(session.is_top2.clone()));
    param.push(DbParamType::Bool(session.is_disturb2.clone()));
    param.push(DbParamType::DateTime2(&session.update_time));
    param.push(DbParamType::Byte(session.status.clone() as i8));
    param.push(DbParamType::Uint(session.id.clone().unwrap()));

    log::info!("db update session params: {:?}", param);

    db.exec2("session update", sql.to_string(), param);
}

pub fn session_updates(db: Db, sessions: &mut Vec<&mut ImSession>) {
    if sessions.is_empty() { return;}
    let sql = "update im_session set name=?, serverId = ?, latelyMessage = ?, hotMessageTotal = ?, isWaking = ?, isTop = ?, isDisturb = ?,
        isWaking2 = ?, isTop2 = ?, isDisturb2 = ?, updateTime = ?, status=? where id = ?";
    let mut params = vec![];
    for session in sessions.iter() {
        let mut param = vec![];
        param.push(DbParamType::Str(&*session.name));
        param.push(DbParamType::OptionStr(session.server_id.as_ref()));
        param.push(DbParamType::Str(&*session.lately_message));
        param.push(DbParamType::Uint(u64::from(session.hot_message_total)));
        param.push(DbParamType::Bool(session.is_waking.clone()));
        param.push(DbParamType::Bool(session.is_top.clone()));
        param.push(DbParamType::Bool(session.is_disturb.clone()));
        param.push(DbParamType::Bool(session.is_waking2.clone()));
        param.push(DbParamType::Bool(session.is_top2.clone()));
        param.push(DbParamType::Bool(session.is_disturb2.clone()));
        param.push(DbParamType::DateTime2(&session.update_time));
        param.push(DbParamType::Int(session.status.clone() as i64));
        param.push(DbParamType::Uint(session.id.clone().unwrap()));

        params.push(param);
    }
    log::info!("session updates params: {:?}", params);

    db.batch_exec("session updates", sql.to_string(), params);
}

pub fn incr_hot_message_num(db: Db, session_id: u64, size: i32) {
    let sql = "update im_session set hotMessageTotal = hotMessageTotal + ?, updateTime=? where id = ?";

    let mut params = vec![];
    params.push(DbParamType::Int(size as i64));
    params.push(DbParamType::DateTime(Local::now()));
    params.push(DbParamType::Uint(session_id));

    log::info!("incr hot message num params: {:?}", params);
    db.exec2("session incr", sql.to_string(), params);
}

pub fn set_hot_message_num(db: Db, session_id: u64, size: i32) {
    let sql = "update im_session set hotMessageTotal = ?, updateTime=? where id = ?";

    let mut params = vec![];
    params.push(DbParamType::Int(size as i64));
    params.push(DbParamType::DateTime(Local::now()));
    params.push(DbParamType::Uint(session_id));
    
    log::info!("hot message num params: {:?}", params);
    db.exec2("session set", sql.to_string(), params);
}

pub fn session_by_user_id_and_time_and_size(db: Db, user_id: String, start_time: DateTime<Local>, start: usize, size: usize)-> Vec<ImSession> {
    let sql = format!("select {} from im_session  where currentUserId=? and (status not in(2)) and updateTime > ?
        order by hotMessageTotal desc, updateTime desc limit {}, {}", SELECT_COLUMNS, start, size);

    let mut params = vec![];
    params.push(DbParamType::String(user_id));
    params.push(DbParamType::DateTime(start_time));
    db.query2("session query", sql.as_str(), params, Box::new(session_wrap))
}

pub fn session_all(db: Db, user_id: &str, start: usize, size: usize)-> Vec<ImSession> {
    let sql = format!("select {} from im_session where currentUserId = ?
        order by hotMessageTotal desc, updateTime desc limit {}, {}", SELECT_COLUMNS, start, size);

    let mut params = vec![];
    params.push(DbParamType::Str(user_id));
    db.query2("session query5", sql.as_str(), params, Box::new(session_wrap))
}

pub fn session_get_by_group_code_and_user_id(db: Db, group_code: &str, user_id: &str) -> Option<ImSession> {
    let sql = format!("select {} from im_session where groupCode = ? and currentUserId = ?", SELECT_COLUMNS);

    let mut params = vec![];
    params.push(DbParamType::Str(group_code));
    params.push(DbParamType::Str(user_id));
    db.query_one("session query2", sql, params, Box::new(session_wrap))
}

pub fn session_get_by_user_id(db: Db, current_user_id: &str, from_user_id: &str, to_user_id: &str) -> Option<ImSession> {
    let sql = format!("select {} from im_session where currentUserId = ? and ((fromUserId = ? and toUserId = ?) or (fromUserId = ? and toUserId = ?))", SELECT_COLUMNS);

    let mut params = vec![];
    params.push(DbParamType::Str(current_user_id));
    params.push(DbParamType::Str(from_user_id));
    params.push(DbParamType::Str(to_user_id));
    params.push(DbParamType::Str(to_user_id));
    params.push(DbParamType::Str(from_user_id));
    db.query_one("session query3", sql.to_string(), params, Box::new(session_wrap))
}

pub fn session_get_by_id(db: Db, id: u64) -> Option<ImSession> {
    let sql = format!("select {} from im_session where id = ?", SELECT_COLUMNS);

    let mut params = vec![];
    params.push(DbParamType::Uint(id));
    db.query_one("session query3", sql.to_string(), params, Box::new(session_wrap))
}

pub fn session_total(db: Db)-> usize {
    let size = db.count("session total",c"select count(*) from im_session", Vec::new());
    log::info!("db session count: {}", size);
    size
}

fn session_wrap(stmt: *mut sqlite3_stmt)-> ImSession {
    unsafe  {
        let mut adder = Adder::new();
        let id = db_get_int64(stmt, adder.add());
        let name = db_get_string(stmt, adder.add()).unwrap();
        let server_id = db_get_string(stmt, adder.add());
        let current_user_id = db_get_string(stmt, adder.add()).unwrap();
        let from_user_id = db_get_string(stmt, adder.add()).unwrap();
        let to_user_id = db_get_string(stmt, adder.add());
        let group_code = db_get_string(stmt, adder.add());
        let session_type = db_get_int(stmt, adder.add());
        let hot_message_total = db_get_int(stmt, adder.add()) as u32;
        let lately_message = db_get_string(stmt, adder.add()).unwrap();
        let status = db_get_int(stmt, adder.add());
        let is_waking = db_get_bool(stmt, adder.add());
        let is_top = db_get_bool(stmt, adder.add());
        let is_disturb = db_get_bool(stmt, adder.add());
        let is_waking2 = db_get_bool(stmt, adder.add());
        let is_top2 = db_get_bool(stmt, adder.add());
        let is_disturb2 = db_get_bool(stmt, adder.add());
        let ext1 = db_get_string(stmt, adder.add());
        let ext2 = db_get_string(stmt, adder.add());

        let create_time_str = db_get_string(stmt, adder.add()).unwrap();
        let update_time_str = db_get_string(stmt, adder.add()).unwrap();

        log::info!("session type and status, {}, {}", session_type, status);
        let session_type2 = serde_json::from_str(session_type.to_string().as_str()).unwrap();
        let status2 = serde_json::from_str(status.to_string().as_str()).unwrap();
        ImSession {
            id: Some(id),
            current_user_id,
            name,
            from_user_id,
            to_user_id,
            server_id,
            session_type: session_type2,
            group_code,
            hot_message_total,
            lately_message,
            is_waking,
            is_top,
            is_disturb,
            is_waking2,
            is_top2,
            is_disturb2,
            ext1,
            ext2,
            status: status2,
            create_time: parse_to_time(create_time_str),
            update_time: parse_to_time(update_time_str),
            to_user: None,
            group: None,
        }
    }
}